using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace Microsoft.MSE.Data
{
    /// <summary>
    /// Template Design Pattern (GoF)
    /// </summary>
    public abstract class StoredProcedure
    {
        private SqlCommand _command;
        private readonly List<string> _parameters = new List<string>();
        private int _returnValue;

        protected StoredProcedure()
        {
            IsolationLevel = IsolationLevel.ReadCommitted;
        }

        protected abstract string Name { get; }

        private SqlCommand Command
        {
            get
            {
                if (_command == null)
                {
                    _command = CreateCommand();
                }

                return _command;
            }
        }

        protected void SetValue(string name, DbType dbType, object value)
        {
            SetValue(name, dbType, value, 0, ParameterDirection.Input);
        }

        protected void SetValue(string name, DbType dbType, object value, ParameterDirection direction)
        {
            SetValue(name, dbType, value, 0, direction);
        }

        protected void SetValue(string name, DbType dbType, object value, int size)
        {
            SetValue(name, dbType, value, size, ParameterDirection.Input);
        }

        protected void SetValue(string name, DbType dbType, object value, int size, ParameterDirection direction)
        {
            if (value is INullable)
            {
                INullable n = (INullable) value;

                if (n.IsNull)
                {
                    value = DBNull.Value;
                }

                //need to do something			    
            }

            if (_parameters.Contains(name))
            {
                Command.Parameters[name].Value = value;
            }
            else
            {
                DbParameter p = Command.CreateParameter();
                p.ParameterName = name;
                p.DbType = dbType;
                p.Size = size;
                p.Direction = direction;
                p.Value = value;
                Command.Parameters.Add(p);
                _parameters.Add(name);
            }
        }

        public T? GetNullable<T>(string paramName) where T : struct
        {
            object value = Command.Parameters[paramName].Value;

            T? data = null;

            if (value == null) return data;
            if (value is DBNull) return data;
            if (value is T) return (T) value;
            if (value is T?) return (T?) value;

            throw new InvalidCastException();
        }

        protected virtual void SetValues()
        {
        }

        protected virtual void GetValues()
        {
        }

        public SqlConnection Connection { get; set; }

        public SqlTransaction Transaction { get; set; }

        public IsolationLevel IsolationLevel { get; set; }

        private SqlCommand CreateCommand()
        {
            SqlCommand cmd = Connection.CreateCommand();
            cmd.CommandText = Name;
            cmd.Connection = Connection;

            SqlParameter p = cmd.CreateParameter();
            p.ParameterName = "@RETURN_VALUE";
            p.DbType = DbType.Int32;
            p.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(p);
            return cmd;
        }

        public int ReturnValue
        {
            get { return _returnValue; }
        }

        public void ExecuteNonQuery()
        {
            SetValues();
            Command.ExecuteNonQuery();
            GetReturnValue();
            GetValues();
        }

        public DataReader ExecuteReader()
        {
            SetValues();
            DataReader reader = new DataReader(Command.ExecuteReader());
            reader.DataReaderClosed += UpdateOutputParameters;
            return reader;
        }

        private void UpdateOutputParameters()
        {
            GetReturnValue();
            GetValues();
        }

        public ArrayList ExecuteCollection(Type type)
        {
            using (DataReader reader = ExecuteReader())
            {
                return DataMapper.ExecuteCollection(type, reader);
            }
        }

        private void GetReturnValue()
        {
            object retval = Command.Parameters["@RETURN_VALUE"].Value;

            if (retval == null)
            {
                _returnValue = 0;
                return;
            }

            if (retval == DBNull.Value)
            {
                _returnValue = 0;
                return;
            }

            _returnValue = (int) retval;
        }
    }
}